﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.OleDb;

namespace DAO
{
    public class BaoCaoDAO:AbtractDAO
    {
        public static void themBaoCao (long maphong,string tenloaiphong,string ngaybd,int songay,double tien,string ngaylaphoadon)
        {
            OleDbConnection ketNoi = null;
            try
            {
                ketNoi = KetNoi();
                string chuoiLenh = "INSERT INTO BAOCAO(MaPhong,TenLoaiPhong,NgayBatDauThue,SoNgayThue,ThanhTien,NgayLapHoaDon) VALUES(@MaPhong,@TenLoaiPhong,@NgayBatDauThue,@SoNgayThue,@ThanhTien,@NgayLapHoaDon)";

                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);

                OleDbParameter thamSo;

                thamSo = new OleDbParameter("@MaPhong", OleDbType.Integer);
                thamSo.Value = maphong;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@TenLoaiPhong", OleDbType.VarChar);
                thamSo.Value = tenloaiphong;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@NgayBatDauThue", OleDbType.Date);
                thamSo.Value = ngaybd;
                lenh.Parameters.Add(thamSo);             

                thamSo = new OleDbParameter("@SoNgayThue", OleDbType.Integer);
                thamSo.Value = songay;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@ThanhTien", OleDbType.Double);
                thamSo.Value = tien;
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@NgayLapHoaDon", OleDbType.Date);
                thamSo.Value = ngaylaphoadon;
                lenh.Parameters.Add(thamSo);

                lenh.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
        }
        public static List<BaoCaoDTO> layDSBaoCaoTheoThangvaNam(string thang, string nam)
        {
            OleDbConnection ketNoi = null;
            List<BaoCaoDTO> dsBaoCao = new List<BaoCaoDTO>();
            int ngaycuoithang =0;

            switch (Convert.ToInt16(thang))
            {
                case 2:{
                    ngaycuoithang = 28;
                }break;
                case 1 : ngaycuoithang = 31;break;
                case 3: ngaycuoithang = 31; break;
                case 5: ngaycuoithang = 31; break;
                case 7: ngaycuoithang = 31; break;
                case 8: ngaycuoithang = 31; break;
                case 10: ngaycuoithang = 31; break;
                case 12: ngaycuoithang = 31; break;
                
                case 4: ngaycuoithang = 30; break;
                case 6: ngaycuoithang = 30; break;
                case 9: ngaycuoithang = 30; break;
                case 11: ngaycuoithang = 30; break;

            }
            string dauthang = nam+"-"+thang+"-1T01:01:01+7:00";
            string cuoithang = nam + "-" + thang + "-"+ngaycuoithang.ToString()+"T01:01:01+7:00";
            
            try
            {
                ketNoi = AbtractDAO.KetNoi();
                string chuoiLenh = "SELECT * FROM BAOCAO where NgayLapHoaDon between @dauthang and @cuoithang";
                OleDbCommand lenh = new OleDbCommand(chuoiLenh, ketNoi);
                OleDbParameter thamSo;
                thamSo = new OleDbParameter("@dauthang", OleDbType.Date);
                thamSo.Value = DateTime.Parse(dauthang);
                lenh.Parameters.Add(thamSo);

                thamSo = new OleDbParameter("@cuoithang", OleDbType.Date);
                thamSo.Value = DateTime.Parse(cuoithang);
                lenh.Parameters.Add(thamSo);
                OleDbDataReader boLoc = lenh.ExecuteReader();

                while (boLoc.Read())
                {
                    BaoCaoDTO BaoCao = new BaoCaoDTO();
                    BaoCao.MaPhong = boLoc.GetInt32(0);
                    if (!boLoc.IsDBNull(1))
                    {
                        BaoCao.TenLoaiPhong = boLoc.GetString(1);
                    }

                    if (!boLoc.IsDBNull(2))
                    {
                        BaoCao.NgayBatDauThue = boLoc.GetDateTime(2);
                    }

                    if (!boLoc.IsDBNull(3))
                    {
                        BaoCao.SoNgayThue = boLoc.GetInt32(3);
                    }

                    if (!boLoc.IsDBNull(4))
                    {
                        BaoCao.ThanhTien = boLoc.GetDouble(4);
                    }

                    if (!boLoc.IsDBNull(5))
                    {
                        BaoCao.NgayLapHoaDon = boLoc.GetDateTime(5);
                    }

                    dsBaoCao.Add(BaoCao);
                }
            }
            catch (Exception ex)
            {
                dsBaoCao = new List<BaoCaoDTO>();
                throw ex;
            }
            finally
            {
                if (ketNoi != null && ketNoi.State == System.Data.ConnectionState.Open)
                    ketNoi.Close();
            }
            return dsBaoCao;
        }
      
    }
}
